package com.lgx.mall.core.dao.impl;

import java.sql.SQLException;
import java.util.List;
import java.util.Random;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.lgx.common.basic.dao.GeneralDao;
import com.lgx.common.basic.dao.GeneralJdbcDao;
import com.lgx.common.exception.DataException;
import com.lgx.common.tree.TreeUtils;
import com.lgx.common.web.Pagination;
import com.lgx.mall.core.dao.IProductDao;
import com.lgx.mall.core.entity.Product;

/**
 * 
 * @author acer
 *
 */
@Component("productDao")
public class ProductDao extends GeneralDao implements IProductDao {

	protected Logger log = Logger.getLogger(this.getClass());
	
	@Autowired
	private GeneralJdbcDao baseDao;
	
	public GeneralJdbcDao getBaseDao() {
		return baseDao;
	}

	public void setBaseDao(GeneralJdbcDao baseDao) {
		this.baseDao = baseDao;
	}
	
	@Transactional
	public Product createProduct(Product product) throws DataException {
		this.getHibernateTemplate().save(product);
		log.info("[创建商品]:"+product.getId());
		return product;
	}

	@Transactional
	public Product updateProduct(Product product) throws DataException {
		this.getHibernateTemplate().update(product);
		log.info("[更新商品]:"+product.getId());
		return product;
	}

	@Transactional
	public void deleteProduct(Product product) throws DataException {
		log.info("[删除商品]:"+product.getId());
		this.getHibernateTemplate().delete(product);
	}

	public void deleteProduct(long id) throws DataException {
		this.deleteProduct(this.findProductById(id));
		log.info("[删除商品]:"+id);
	}

	public Product findProductById(long id) {
		return this.getHibernateTemplate().get(Product.class, id);
	}

	@SuppressWarnings("unchecked")
	public List<Product> findProductList(String sql) {
		return this.getHibernateTemplate().find(sql);
	}
	
	@SuppressWarnings("unchecked")
	public List<Product> findProductList(){
		String sql =" SELECT PR.* "+
				"   FROM TM_PRODUCT PR "+
				"  ORDER BY PR.ID ";
		List<Product> ca = (List<Product>) baseDao.queryJdbcForList(sql, Product.class);
		return (List<Product>) TreeUtils.addChildren(ca, null, null);
	}
	
	public Pagination findProductPagination(Object[] params, int pageSize, int pageNum){
		String sql = "from Product p ";
		
		if(params.length > 0 && params[0] != null)
		{
			sql += " where p.isdelete = '0' ";
			if(!params[0].equals("")){
				sql += " and p.areaCategory = " + params[0];
			}
			if(!params[1].equals("")){
				sql += " and p.category.id = " + params[1];
			}
			sql += " and p.name like '%"+params[2]+"%'";
			sql += " and p.code like '%"+params[3]+"%'";
		}
		
		sql += " order by p.id asc";
		return this.getListPagination(sql, new Object[]{}, pageSize, pageNum);
	}
	
	public void deleteProducts(String[] ids) throws DataException {
		for(String id : ids)
		{
			this.deleteProduct(Long.parseLong(id));
		}
	}
	/**
	 * 根据categoryId和price查询
	 */

	@SuppressWarnings("unchecked")
	public List<Product> findProductByCidAndPrice(long categoryId, String type) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(categoryId!=0) {
			sql += " and p.category.id="+categoryId;
		}
		if(type!=null||!("").equals(type)) {
			if("1".equals(type)) {
				sql += " and p.price between 1 and 200";
			} else if("2".equals(type)) {
				sql += " and p.price between 200 and 500";
			} else if("3".equals(type)) {
				sql += " and p.price between 500 and 1000";
			} else {
				sql += " and p.price > 1000";
			}	
		}	
		sql += " order by p.id asc";
		return this.getHibernateTemplate().find(sql);
	}
	@SuppressWarnings("unchecked")
	public List<Product> findProductByCidAndPrice2(long categoryId, String type) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(categoryId!=0) {
			sql += " and p.areaCategory="+categoryId;
		}
		if(type!=null&&!("").equals(type)&&!("null").equals(type)) {
			if("1".equals(type)) {
				sql += " and p.price between 1 and 50";
			} else if("2".equals(type)) {
				sql += " and p.price between 50 and 100";
			} else if("3".equals(type)) {
				sql += " and p.price between 100 and 200";
			} else if("4".equals(type)) {
				sql += " and p.price between 200 and 500";
			} else if("5".equals(type)) {
				sql += " and p.price between 500 and 1000";
			} else {
				sql += " and p.price > 1000";
			}	
		}	
		sql += " order by p.id asc";
		return this.getHibernateTemplate().find(sql);
	}


	/**
	 * 查询同所属同品牌的信息
	 */

	public Pagination findProductByCategoryAndPrice(long categoryId, String type, int pageSize, int pageNum) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(categoryId!=0) {
			sql += " and p.areaCategory="+categoryId;
		}
		if(type!=null&&!("").equals(type)&&!("null").equals(type)) {
			if("1".equals(type)) {
				sql += " and p.price between 1 and 200";
			} else if("2".equals(type)) {
				sql += " and p.price between 200 and 500";
			} else if("3".equals(type)) {
				sql += " and p.price between 500 and 1000";
			} else {
				sql += " and p.price > 1000";
			}	
		}	
		sql += " order by p.price asc,p.id asc";
		return this.getListPagination(sql, new Object[]{}, pageSize, pageNum);
	}

	public Pagination findProductByCategoryAndPrice2(String areaNode,long categoryId, String type, String sortNumber,int pageSize, int pageNum) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(areaNode!=null && !(("").equals(areaNode))&&!("null").equals(areaNode)) {
			sql += " and p.areaNode="+areaNode+"";
		}
		if(categoryId!=0) {
			sql += " and p.areaCategory="+categoryId;
		}
		if(type!=null&&!("").equals(type)&&!("null").equals(type)) {
			if("1".equals(type)) {
				sql += " and p.price between 0 and 50";
			} else if("2".equals(type)) {
				sql += " and p.price between 50 and 100";
			} else if("3".equals(type)) {
				sql += " and p.price between 100 and 200";
			} else if("4".equals(type)) {
				sql += " and p.price between 200 and 500";
			} else if("5".equals(type)) {
				sql += " and p.price between 500 and 1000";
			} else if("6".equals(type)) {
				sql += " and p.price > 1000";
			} else {
				sql += " and p.price > 0";
			}
		}	
		if(sortNumber!=null && !(("").equals(sortNumber))&&!("null").equals(sortNumber)) {
			if(sortNumber.equals("0")) {
				sql += " order by p.createdate asc,p.id asc";
			} else {
				sql += " order by p.price asc,p.id asc";
			}
		} else {
			sql += " order by p.price asc,p.id asc";
		}
		
		return this.getListPagination(sql, new Object[]{}, pageSize, pageNum);
	}
	@SuppressWarnings("unchecked")
	public List<Product> findProductByIdAndBrand(long id,long brandId) {
		String sql = "from Product p where p.isdelete='0'";

		if(id!=0) {
			sql += " and p.id!="+id;
		}
		if(brandId!=0) {
			sql += " and p.brand.id="+brandId;
		}
		sql += " order by p.id asc";
		return this.getHibernateTemplate().find(sql);
	}
	
	/**
	 * 分页
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Product> guessYouLike(int pageSize) {
		final int _pageSize = pageSize;
		final String sql = "from Product p where p.isdelete='0' order by p.id";
		
		String counthql = sql.substring(sql.toLowerCase().indexOf("from ")); 
        counthql = "select count(*) "+counthql; 
        final String queryStr = counthql;
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(queryStr);
				List result = query.list();
				return result;
			}
		});
		int totalRecordNum = Integer.parseInt(list.get(0).toString());
		
		int totalPageNum = totalRecordNum/_pageSize;
		if(totalRecordNum%_pageSize!=0) {
			totalPageNum+=1;
		}
		
		Random random = new Random();
		final int currentPage = Math.abs(random.nextInt() % totalPageNum);
		
		List<Product> data = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(sql);
				List result = query.setFirstResult(currentPage).setMaxResults(
						_pageSize).list();
				return result;
			}
		});
		
		return data;
	}
	/**
	 * 商品名称或编号模糊查询
	 */
	@SuppressWarnings("unchecked")
	public List<Product> findProductByNameOrCode(String params) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(params!=null&&!("").equals(params)&&!("null").equals(params)) {
			sql += " and (p.name like '%"+params+"%' or p.code like '%"+params+"%')";
		}
		sql += " order by p.id asc";
		return this.getHibernateTemplate().find(sql);
	}
	/**
	 * 商品：根据名字或编号来模糊查询
	 */
	public Pagination findProductByNameOrCode(String params,String time,String price,int pageSize, int pageNum) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(params!=null&&!("").equals(params)&&!("null").equals(params)) {
			sql += " and (p.name like '%"+params+"%' or p.code like '%"+params+"%')";
		}
		if(time!=null && !(("").equals(time))&&!("null").equals(time)) {
			if(time.indexOf("desc")!=0) {
				sql += " order by p.createdate asc,p.id asc";
			} else {
				sql += " order by p.createdate desc,p.id asc";
			}
		} 
		if(price!=null && !(("").equals(price))&&!("null").equals(price)) {
			if(price.indexOf("desc")!=0) {
				sql += " order by p.price asc,p.id asc";
			} else {
				sql += " order by p.price desc,p.id asc";
			}
		} 
		return this.getListPagination(sql, new Object[]{}, pageSize, pageNum);
	}
	/**
	 * 品牌：根据名字来模糊查询
	 */
	public Pagination findProductByBrandName(String params,String time,String price,int pageSize, int pageNum) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(params!=null&&!("").equals(params)&&!("null").equals(params)) {
			sql += " and p.brand.name like '%"+params+"%' ";
		}
		if(time!=null && !(("").equals(time))&&!("null").equals(time)) {
			if(time.indexOf("desc")!=0) {
				sql += " order by p.createdate asc ,p.id asc";
			} else {
				sql += " order by p.createdate desc ,p.id asc";
			}
		} 
		if(price!=null && !(("").equals(price))&&!("null").equals(price)) {
			if(time.indexOf("desc")!=0) {
				sql += " order by p.price asc ,p.id asc";
			} else {
				sql += " order by p.price desc,p.id asc";
			}
		} 
			
		return this.getListPagination(sql, new Object[]{}, pageSize, pageNum);
	}

	@SuppressWarnings("unchecked")
	public List<Product> findProductByCategory(String areaNode,
			long areaCategory, long categoryId, long brandId) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(!StringUtils.isEmpty(areaNode)){
			sql += " and p.areaNode = '"+areaNode+"'";
		}

		if(areaCategory!=0) {
			sql += " and p.areaCategory = "+areaCategory;
		}
		
		if(categoryId!=0) {
			sql += " and exists (from Category c where c.id = "+categoryId+" and p.category.layerOrder like concat(c.layerOrder,'%'))";
		}
		
		if(brandId!=0) {
			sql += " and p.brand.id="+brandId;
		}
		sql += " order by p.id asc";
		
		return this.getHibernateTemplate().find(sql);
	}

	public Pagination findProductByCategory(String areaNode, long areaCategory,
			long categoryId, long brandId, String time, String price, int pageSize, int pageNum) {
		String sql = "from Product p where p.isdelete='0'";
		
		if(!StringUtils.isEmpty(areaNode)){
			sql += " and p.areaNode = '"+areaNode+"'";
		}

		if(areaCategory!=0) {
			sql += " and p.areaCategory = "+areaCategory;
		}
		
		if(categoryId!=0) {
			sql += " and exists (from Category c where c.id = "+categoryId+" and p.category.layerOrder like concat(c.layerOrder,'%'))";
		}
		
		if(brandId!=0) {
			sql += " and p.brand.id="+brandId;
		}
		if(time!=null && !(("").equals(time))&&!("null").equals(time)) {
			if(time.indexOf("desc")!=0) {
				sql += " order by p.createdate asc ,p.id asc";
			} else {
				sql += " order by p.createdate desc ,p.id asc";
			}
		} 
		if(price!=null && !(("").equals(price))&&!("null").equals(price)) {
			if(time.indexOf("desc")!=0) {
				sql += " order by p.price asc ,p.id asc";
			} else {
				sql += " order by p.price desc,p.id asc";
			}
		} 
		return this.getListPagination(sql, new Object[]{}, pageSize, pageNum);
	}
}
